
/*
'===============================================================================
'  HNG.Portal.BL.TblMediaManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System.Text;

namespace HNG.Portal.BLL
{
	public class TblMediaManager : TblMediaManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblMediaManager()
		{
			// Nothing for now.
		}

        public DataTable be_selectToGridView(TblMediaEntity _TblMediaEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[8];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMediaEntity.SortExpression) ? "[ID]" : _TblMediaEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMediaEntity.SortDirection) ? " DESC" : _TblMediaEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenMedia],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[Link],").Append("\n");
                sbSql.Append("  ch.[CateMediaID],").Append("\n");
                sbSql.Append("  ch.[DinhDang],").Append("\n");
                sbSql.Append("  ch.[CategoryName],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblMedia ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblMediaCategory dmt").Append("\n");
                sbSql.Append("  ON ch.CateMediaID = dmt.ID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblMediaEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@Keyword", _TblMediaEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @Keyword").Append("\n");

                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenMedia] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");

                    sbSql.AppendFormat("  OR ch.[ID] LIKE N'%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenMedia] LIKE N'%{0}%')", _TblMediaEntity.Condition).Append("\n");

                }


                if (!string.IsNullOrEmpty(_TblMediaEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblMediaEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.ViTri))
                {
                    _SqlParameter[2] = new SqlParameter("@ViTri", _TblMediaEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Type))
                {
                    _SqlParameter[3] = new SqlParameter("@Type", _TblMediaEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Status))
                {
                    _SqlParameter[4] = new SqlParameter("@TrangThai", _TblMediaEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }
                if (!string.IsNullOrEmpty(_TblMediaEntity.DinhDang))
                {
                    _SqlParameter[5] = new SqlParameter("@DinhDang", _TblMediaEntity.DinhDang);
                    sbSql.Append("  AND ch.[DinhDang] LIKE @DinhDang").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.DM))
                {
                    _SqlParameter[6] = new SqlParameter("@DM", _TblMediaEntity.DM);
                    sbSql.Append("  AND ch.[CateMediaID] LIKE @DM").Append("\n");
                }
                if (!string.IsNullOrEmpty(_TblMediaEntity.ProductID))
                {
                    _SqlParameter[7] = new SqlParameter("@ProductID", _TblMediaEntity.ProductID);
                    sbSql.Append("  AND ch.[NhomFileID] LIKE @ProductID").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMediaEntity.StartIndex, _TblMediaEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable fe_selectMedia(TblMediaEntity _TblMediaEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[7];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMediaEntity.SortExpression) ? "[ID]" : _TblMediaEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMediaEntity.SortDirection) ? " DESC" : _TblMediaEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenMedia],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[Link],").Append("\n");
                sbSql.Append("  ch.[CateMediaID],").Append("\n");
                sbSql.Append("  ch.[NhomFileID],").Append("\n");
                sbSql.Append("  ch.[DinhDang],").Append("\n");
                sbSql.Append("  ch.[NgayDang],").Append("\n");
                sbSql.Append("  ch.[TenNhomFile],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblMedia ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblMediaCategory dmt").Append("\n");
                sbSql.Append("  ON ch.CateMediaID = dmt.ID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblMediaEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@Keyword", _TblMediaEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @Keyword").Append("\n");

                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenMedia] LIKE '%{0}%'", _TblMediaEntity.Condition).Append("\n");

                    sbSql.AppendFormat("  OR ch.[ID] LIKE N'%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%{0}%'", _TblMediaEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenMedia] LIKE N'%{0}%')", _TblMediaEntity.Condition).Append("\n");

                }


                if (!string.IsNullOrEmpty(_TblMediaEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblMediaEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.ViTri))
                {
                    _SqlParameter[2] = new SqlParameter("@ViTri", _TblMediaEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Type))
                {
                    _SqlParameter[3] = new SqlParameter("@Type", _TblMediaEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.Status))
                {
                    _SqlParameter[4] = new SqlParameter("@TrangThai", _TblMediaEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }
                if (!string.IsNullOrEmpty(_TblMediaEntity.DinhDang))
                {
                    _SqlParameter[5] = new SqlParameter("@DinhDang", _TblMediaEntity.DinhDang);
                    sbSql.Append("  AND ch.[DinhDang] LIKE @DinhDang").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMediaEntity.DM))
                {
                    _SqlParameter[6] = new SqlParameter("@DM", _TblMediaEntity.DM);
                    sbSql.Append("  AND ch.[CateMediaID] LIKE @DM").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMediaEntity.StartIndex, _TblMediaEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }
	}
}
